package school.repository;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import school.domain.Lesson;

import com.ibm.db2.jcc.a.SqlException;

public class JdbcLessonDao implements LessonDao {

	@Override
	public ArrayList<Lesson> getByCourseId(int courseId) throws Exception {
		Connection con = JdbcUtil.connect();
		ArrayList<Lesson> lessons = new ArrayList<Lesson>();
		
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

		String sql = "CALL Get_Lessons(?)";
		CallableStatement prepCall = con.prepareCall(sql);

		prepCall.setInt(1, courseId);

		try {
			ResultSet rs = prepCall.executeQuery();

			while(rs.next()) {
				Lesson lesson = new Lesson();
				lesson.setCoursesId(courseId);
				lesson.setVehicleId(rs.getInt(1));
				lesson.setCategory(rs.getString(2));
				lesson.setVehicleDetails(rs.getString(3)+", "+String.format("%d",rs.getInt(4)));
				lesson.setLessonDate(dateFormat.format(rs.getDate(5)));
				lesson.setLessonStatus(rs.getString(6));
				lesson.setLessonTime(rs.getTime(7).toString());
				lesson.setPrice(rs.getDouble(8));
				lesson.setStaffId(rs.getInt(9));
				lesson.setStaffName(rs.getString(10));
				lesson.setId(rs.getInt(11));
				
				lessons.add(lesson);
			}
		} catch (SqlException e) {
			e.printStackTrace();
			throw e;
		} catch (Exception e) {
			throw e;
		} finally {
			JdbcUtil.disconnect();
		}

		System.out.println("JdbcLessonDao: "+lessons.size());
		return lessons;
	}

	@Override
	public void cancelLesson(int lessonId, int customerId) throws Exception {
		Connection con = JdbcUtil.connect();
		
		String sql = "CALL Cancel_Lesson(?,?)";
		CallableStatement prepCall = con.prepareCall(sql);

		prepCall.setInt(1, lessonId);	
		prepCall.setInt(2, customerId);
		
		try {
			prepCall.execute();
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			JdbcUtil.disconnect();
		}
	}

	@Override
	public ArrayList<Lesson> getAvailableForCustomer(int customerId)
			throws Exception {
		Connection con = JdbcUtil.connect();
		ArrayList<Lesson> lessons = new ArrayList<Lesson>();
		
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

		String sql = "CALL Get_Lessons_By_Customer(?)";
		CallableStatement prepCall = con.prepareCall(sql);

		prepCall.setInt(1, customerId);

		try {
			ResultSet rs = prepCall.executeQuery();

			while(rs.next()) {
				Lesson lesson = new Lesson();
				//lesson.setCoursesId(rs.getInt(12));
				lesson.setVehicleId(rs.getInt(1));
				lesson.setCategory(rs.getString(2));
				lesson.setVehicleDetails(rs.getString(3)+", "+String.format("%d",rs.getInt(4)));
				lesson.setLessonDate(dateFormat.format(rs.getDate(5)));
				lesson.setLessonStatus(rs.getString(6));
				lesson.setLessonTime(rs.getTime(7).toString());
				lesson.setPrice(rs.getDouble(8));
				lesson.setStaffId(rs.getInt(9));
				lesson.setStaffName(rs.getString(10));
				lesson.setId(rs.getInt(11));
				
				lessons.add(lesson);
			}
		} catch (SqlException e) {
			e.printStackTrace();
			throw e;
		} catch (Exception e) {
			throw e;
		} finally {
			JdbcUtil.disconnect();
		}

		System.out.println("JdbcLessonDao: "+lessons.size());
		return lessons;
	}	
}
